-- @owner: wan005
-- @date: 2024-08-12
-- @testpoint: coalesce()函数作为对象名的测试，部分测试点合理报错

--prestep1:切换B库;expect:执行成功
@conn PrimaryDbBmode;
show sql_compatibility;

--step1: 作为列名; expect: 成功
drop table if exists coalesce_test cascade;
create table coalesce_test(coalesce text);
insert into coalesce_test values('0');

--step2: 作为索引名; expect: 成功
create index on coalesce_test(coalesce);

--step3: 作为表名; expect: 成功
drop table if exists coalesce cascade;
create table coalesce(coalesce text);
drop table if exists coalesce cascade;

--step4: 作为数据库名; expect: 成功
drop database if exists coalesce;
create database coalesce;
drop database coalesce;

--step5: 作为外部数据源名; expect: 成功
drop data source if exists coalesce;
create data source coalesce;
drop data source coalesce;

--step6: 作为自定义函数名; expect: 报错
create or replace function coalesce(c1 int)
returns text
as  $$
begin
    return c1;
end;
$$language plpgsql;
/

--step7: 作为存储过程名; expect: 报错
drop table if exists t_coalesce_0025;
create table t_coalesce_0025(c1 text);
CREATE OR REPLACE PROCEDURE coalesce(P1 IN text) AS
BEGIN
insert into t_coalesce_0025 values ('00001');
raise info '测试coalesce作为存储过程名';
END;
/

--step8: 作为角色名; expect: 成功
drop role if exists coalesce;
create role coalesce with password 'gauss@123' valid until '2001-12-12';
drop role coalesce;

--step9: 作为用户组名; expect: 成功
drop group if exists coalesce;
create group coalesce with password 'gauss@123';
drop group coalesce;

--step10: 作为模式名; expect: 成功
drop schema if exists coalesce;
create schema coalesce;
drop schema coalesce;

--step11: 作为序列名; expect: 成功
create sequence public.coalesce start 100 cache 50;
drop sequence public.coalesce;

--step12: 作为用户名; expect: 成功
drop user if exists coalesce;
CREATE USER coalesce PASSWORD 'Bigdata@123';
drop user coalesce;

--step13: 作为游标名; expect: 成功
drop table if exists coalesce_test cascade;
create table coalesce_test(cid int,fid int);
start transaction;
cursor coalesce for select * from coalesce_test order by 1;
close coalesce;
end;

--step14: 清理环境; expect: 成功
drop table if exists coalesce_test cascade;
drop table if exists t_coalesce_0025;